﻿using DSPL.Contracts;
using DSPL.DataAccess;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DSPL.Contracts.Common;

namespace DSPL.Services.Help
{
    public class HelpBuilder
    {
        public Query GetHelpBuilder(HelpNames helpName)
        {
            Query query = new Query();

            switch (helpName)
            {
                case HelpNames.AreaHelp:
                    query = LoadAreaHelp();
                    break;

                case HelpNames.SalesRepHelp:
                    query = LoadSalesRepHelp();
                    break;
                case HelpNames.RankHelp:
                    query = LoadRankHelp();
                    break;

                case HelpNames.DebtorGrpHelp:
                    query = LoadDebtorGrpHelp();
                    break;

                case HelpNames.PriceLevelHelp:
                    query = LoadPriceLevelHelp();
                    break;

                case HelpNames.TaxHelp:
                    query = LoadTaxHelp();
                    break;

                case HelpNames.BankAccountsHelp:
                    query = LoadBankAccountsHelp();
                    break;

                case HelpNames.ProductGroupHelp:
                    query = LoadGroupHelp();
                    break;

                case HelpNames.TypeHelp:
                    query = LoadTypeHelp();
                    break;

                case HelpNames.TaxHedHelp:
                    query = LoadTaxHedHelp();
                    break;

                case HelpNames.UnitHelp:
                    query = LoadUnitHelp();
                    break;

                case HelpNames.PriLvlHelp:
                    query = LoadPriLvlHelp();
                    break;

                case HelpNames.AltPriLvlHelp:
                    query = LoadAlternativePriLvlHelp();
                    break;

                case HelpNames.PurchaseAcHelp:
                    query = LoadPurchaseAccHelp();
                    break;

                case HelpNames.SalesAccHelp:
                    query = LoadSalesAccHelp();
                    break;

                case HelpNames.DiscountAccHelp:
                    query = LoadDiscountAccHelp();
                    break;

                case HelpNames.StockInHandAccHelp:
                    query = LoadStockInHandHelp();
                    break;

                case HelpNames.CurrencyHelp:
                    query = LoadCurrencyHelp();
                    break;

                case HelpNames.PettyCashHelp:
                    query = LoadPettyCashAccHelp();
                    break;

                case HelpNames.CreditorGrpHelp:
                    query = LoadCreditorGroupHelp();
                    break;

                case HelpNames.SupplierHelp:
                    query = LoadSupplierHelp();
                    break;

                case HelpNames.ActiveSupplierAllDetailsHelp:
                    query = LoadActiveSupplierAllDetailsHelp();
                    break;

                case HelpNames.AltSupplierHelp:
                    query = LoadAlternativeSupplierHelp();
                    break;

                case HelpNames.UserCostHelp:
                    query = LoadUserCostHelp();
                    break;

                case HelpNames.LocationHelp:
                    query = LoadLocationHelp();
                    break;

                case HelpNames.AllLocationsHelp:
                    query = LoadAllLocationHelp();
                    break;

                case HelpNames.DebtorHelp:
                    query = LoadDebtorHelp();
                    break;

                case HelpNames.ActiveDebtorAllDetailsHelp:
                    query = LoadAllActiveDebtorDetailsHelp();
                    break;

                case HelpNames.ReasonHelp:
                    query = LoadReasonHelp();
                    break;

                case HelpNames.TaxReceivableAccHelp:
                    query = LoadTaxReceivableAccHelp();
                    break;

                case HelpNames.ItemsHelp:
                    query = LoadItemsHelp();
                    break;

                case HelpNames.GRNHedHelp:
                    query = LoadGRNHedHelp();
                    break;

                case HelpNames.CostCentreHelp:
                    query = LoadCostCentreHelp();
                    break;

                case HelpNames.AllSalesRepHelp:
                    query = LoadAllSalesRepHelp();
                    break;

                case HelpNames.BankHelp:
                    query = LoadBankHelp();
                    break;

                case HelpNames.CostCenterDescriptiveHelp:
                    query = LoadCostCenterDescriptiveHelp();
                    break;

                case HelpNames.EditPurchaseOrderHelp:
                    query = LoadEditPurchaseOrderHelp();
                    break;

                case HelpNames.AllocatePurchaseOrderHelp:
                    query = LoadAllocatePurchaseOrderHelp();
                    break;

                case HelpNames.ApprovedPurchaseOrderHelp:
                    query = LoadApprovedPurchaseOrderHelp();
                    break;

                case HelpNames.UnApprovedPurchaseOrderHelp:
                    query = LoadUnApprovedPurchaseOrderHelp();
                    break;

                case HelpNames.AllPurchaseOrderHelp:
                case HelpNames.AllTempPurchaseOrderHelp:
                    query = LoadAllPurchaseOrderHelp(helpName);
                    break;

                    //query = LoadAllTempPurchaseOrderHelp();
                    //break;

                case HelpNames.AllGoodReceiveNoteHelp:
                case HelpNames.TemporaryGoodReceiveNoteHelp:
                    query = LoadAllGoodReceiveNoteHelp();
                    break;

                //case HelpNames.TemporaryGoodReceiveNoteHelp:
                //    query = LoadTemporaryGoodReceiveNoteHelp();
                //    break;

                case HelpNames.AllGoodReceiveReturnNoteHelp:
                    query = LoadAllGoodReceiveReturnNoteHelp();
                    break;

                case HelpNames.GRNRStkHelp:
                    query = LoadGRNRStkHelp();
                    break;

                case HelpNames.AllQuotationHelp:
                    query = LoadAllQuotationHelp();
                    break;

                case HelpNames.PoHedHelp:
                    query = LoadPoHedHelp();
                    break;

                case HelpNames.SalesOrderHelp:
                    query = LoadAllSalesOrderHelp();
                    break;   

            }
            return query;
        }

        public Query GetHelpBuilder(HelpNames helpName, object primaryKey)
        {
            Query query = new Query();

            switch (helpName)
            {
                case HelpNames.PoDetHelp:
                case HelpNames.PoDetHelpTemp:
                    query = LoadPurchaseOrderDetails(helpName,primaryKey);
                    break;

                
            }
            return query;
        }

        /// <summary>
        /// Get product master details
        /// </summary>
        /// <returns></returns>
        public Query LoadItemsHelp()
        {
            // Select ItemCode,ItemName From fItems Where ItemStatus<>1
            // ORDER BY ItemCode
            ///
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "ItemPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "ItemCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "ItemName" });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "ItemCode", OrderBy = QOrderby.ASCENDING });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });


            Query query = new Query() { TableName = "fItems", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        private Query LoadAreaHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "AreaPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "AreaCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "AreaName" });
            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "AreaCode" });

            Query query = new Query() { TableName = "fArea", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        private Query LoadSalesRepHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "RepPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "RepCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "RepName" });
            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "RepCode" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            Query query = new Query() { TableName = "fSalRep", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray(), WhereClause = whereList.ToArray() };

            return query;
        }

        private Query LoadRankHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "RankPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "RankCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "RankDesc" });
            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "RankCode" });

            Query query = new Query() { TableName = "fRank", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        /// <summary>
        /// Get debtor group details
        /// </summary>
        /// <returns></returns>
        private Query LoadDebtorGrpHelp()
        {

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "DbGrPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "DbGrCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "DbGrName" });
            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "DbGrCode" });

            Query query = new Query() { TableName = "fDebGrp", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;
        }

        // Get Price Level details
        private Query LoadPriceLevelHelp()
        {
            //  string sqlPriceLevel = "Select a.PrilCode,a.PrilName from fPriLvl a Inner join fCurrency b ON a.CurPk=b.CurPk WHERE b.CurCode =@curCode order by a.PrilCode";

            //var parametersPriceLevel = new List<Parameter>
            //                     {
            //                         new Parameter{ParamName="@curCode",ParamValue=GlobalVariables.BaseCurrency},
            //                     };

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PrilCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PrilName" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fCurrency.CurCode", Comparison = QComparison.EQUALS, ColumnValue = GlobalVariables.BaseCurrency });
            whereList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PrilCode", OrderBy = QOrderby.ASCENDING });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fPriLvl", ToTable = "fCurrency", FromColumn = "CurPk", ToColumn = "CurPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            Query query = new Query() { TableName = "fPriLvl", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray(), JoinStructure = joinList.ToArray() };
            return query;

        }

        /// <summary>
        /// Get Tax details
        /// </summary>
        /// <returns></returns>
        public Query LoadTaxHelp()
        {

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "TaxPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "TaxCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "TaxName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "TaxPer" });
            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "TaxCode" });

            Query query = new Query() { TableName = "fTax", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query; ;
        }

        public Query LoadBankAccountsHelp()
        {

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccDesc" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fUsrCosDet.UserId", Comparison = QComparison.EQUALS, ColumnValue = GlobalVariables.LoggedInUser });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfsc.AccTyp", Comparison = QComparison.EQUALS, ColumnValue = "B" });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCat", Comparison = QComparison.EQUALS, ColumnValue = 1 });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.ConAcc", Comparison = QComparison.EQUALS, ColumnValue = "Y", ColumnConnector = QLogicOperator.AND });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fGlfAcc", ToTable = "fUsrCosDet", FromColumn = "CostPk", ToColumn = "CostPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });
            joinList.Add(new ColumnWithValue { FromTable = "fGlfAcc", ToTable = "fGlfsc", FromColumn = "AccSortPk", ToColumn = "AccSortPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            Query query = new Query() { TableName = "fGlfAcc", QType = QueryType.SELECTDISTINCT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray(), JoinStructure = joinList.ToArray(), WhereClause = whereList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get group details
        /// </summary>
        /// <returns></returns>
        private Query LoadGroupHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "GroupPk", ColumnValue = "GroupPk" });
            columnList.Add(new ColumnWithValue { ColumnName = "GroupCode", ColumnValue = "GroupCode" });
            columnList.Add(new ColumnWithValue { ColumnName = "GroupName", ColumnValue = "GroupName" });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue { ColumnName = "GroupCode", OrderBy = QOrderby.ASCENDING });


            Query query = new Query() { TableName = "fGroup", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get Type  details
        /// </summary>
        /// <returns></returns>
        private Query LoadTypeHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "TypePk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "TypeCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "TypeName" });
            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "TypeCode" });
            Query query = new Query() { TableName = "fType", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;

        }

        /// <summary>
        /// Get Tax Hed details
        /// </summary>
        /// <returns></returns>
        private Query LoadTaxHedHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "TaxComPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "TaxComCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "TaxComName" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "TaxComCode" });
            Query query = new Query() { TableName = "fTaxHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray(), WhereClause = whereList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get Unit details
        /// </summary>
        /// <returns></returns>
        private Query LoadUnitHelp()
        {

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "UnitPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "UnitCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "UnitName" });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "UnitCode" });
            Query query = new Query() { TableName = "fUnit", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get Price Level details
        /// </summary>
        /// <returns></returns>
        private Query LoadPriLvlHelp()
        {

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PriLvlPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PrilCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PrilName" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fCurrency.CurCode", Comparison = QComparison.EQUALS, ColumnValue = GlobalVariables.BaseCurrency });
            whereList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fPriLvl", ToTable = "fCurrency", FromColumn = "CurPk", ToColumn = "CurPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PrilCode", OrderBy = QOrderby.ASCENDING });

            Query query = new Query() { TableName = "fPriLvl", ColValues = columnList.ToArray(), QType = QueryType.SELECT, WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;

        }

        // Get Price Level details - Alternative
        private Query LoadAlternativePriLvlHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PriLvlPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PrilCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PrilName" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fPriLvl", ToTable = "fCurrency", FromColumn = "CurPk", ToColumn = "CurPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fPriLvl.PrilCode", OrderBy = QOrderby.ASCENDING });

            Query query = new Query() { TableName = "fPriLvl", ColValues = columnList.ToArray(), QType = QueryType.SELECT, JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;

        }

        /// <summary>
        /// Get Purchase Accounts
        /// </summary>
        /// <returns></returns>

        private Query LoadPurchaseAccHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fConAcc.AccCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fConAcc.AccDesc" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fConAcc.AccCat", Comparison = QComparison.EQUALS, ColumnValue = 1, Level = 0 });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfSc.AccTyp", Comparison = QComparison.EQUALS, ColumnValue = "P", Level = 1, ColumnConnector = QLogicOperator.AND });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfSc.AccTyp", Comparison = QComparison.EQUALS, ColumnValue = "M", ColumnConnector = QLogicOperator.OR, Level = 1 });
            whereList.Add(new ColumnWithValue() { ColumnName = "fConAcc.ConAcc", Comparison = QComparison.EQUALS, ColumnValue = "Y", Level = 0, ColumnConnector = QLogicOperator.AND });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fConAcc", ToTable = "fGlfSc", FromColumn = "AccSortPk", ToColumn = "AccSortPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            Query query = new Query() { TableName = "fConAcc", ColValues = columnList.ToArray(), QType = QueryType.SELECT, WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };

            return query;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private Query LoadAccCashBankHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccDesc" });

            List<ColumnWithValue> joinStructure = new List<ColumnWithValue>();
            joinStructure.Add(new ColumnWithValue { FromTable = "fGlfAcc", FromColumn = "CostPk", ToTable = "fUsrCosDet", ToColumn = "CostPk", JoinType = QJoinType.INNERJOIN });

            List<ColumnWithValue> whereClause = new List<ColumnWithValue>();
            whereClause.Add(new ColumnWithValue { ColumnName = "fUsrCosDet.UserId", Level = 0, ColumnValue = GlobalVariables.LoggedInUser, Comparison = QComparison.EQUALS });
            whereClause.Add(new ColumnWithValue { ColumnName = "fGlfAcc.AccCat", Level = 1, ColumnValue = 2, ColumnConnector = QLogicOperator.AND, Comparison = QComparison.EQUALS });
            whereClause.Add(new ColumnWithValue { ColumnName = "fGlfAcc.AccCat", Level = 1, ColumnValue = 4, ColumnConnector = QLogicOperator.OR, Comparison = QComparison.EQUALS });
            whereClause.Add(new ColumnWithValue { ColumnName = "fGlfAcc.ConAcc", Level = 0, ColumnValue = "Y", ColumnConnector = QLogicOperator.AND, Comparison = QComparison.NOTEQUAL });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode" });

            Query query = new Query() { ColValues = columnList.ToArray(), QType = QueryType.SELECTDISTINCT, TableName = "fGlfAcc", JoinStructure = joinStructure.ToArray(), WhereClause = whereClause.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get Sales Accounts
        /// </summary>
        /// <returns></returns>

        public Query LoadSalesAccHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fConAcc.AccCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fConAcc.AccDesc" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfSc.AccTyp", Comparison = QComparison.EQUALS, ColumnValue = "P", ColumnConnector = QLogicOperator.AND });
            whereList.Add(new ColumnWithValue() { ColumnName = "fConAcc.AccCat", Comparison = QComparison.EQUALS, ColumnValue = 1, ColumnConnector = QLogicOperator.AND });
            whereList.Add(new ColumnWithValue() { ColumnName = "fConAcc.ConAcc", Comparison = QComparison.EQUALS, ColumnValue = "Y" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fConAcc", FromColumn = "AccSortPk", ToTable = "fGlfSc", ToColumn = "AccSortPk", JoinType = QJoinType.INNERJOIN });

            Query query = new Query() { TableName = "fConAcc", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };
            return query;

        }

        /// <summary>
        /// Get Discount Accounts
        /// </summary>
        /// <returns></returns>

        public Query LoadDiscountAccHelp()
        {
            // "Select AccCode,AccDesc From fConAcc Where ConAcc=@controlAccount";                       

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "AccCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "AccDesc" });
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "ConAcc", Comparison = QComparison.EQUALS, ColumnValue = "Y" });

            Query query = new Query() { TableName = "fConAcc", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            return query;

        }

        /// <summary>
        /// Get Stock In Hand account details
        /// </summary>
        /// <returns></returns>
        public Query LoadStockInHandHelp()
        {
            //Select a.AccCode,a.AccDesc From fGlfAcc a,fGlfSc b where  a.AccSortPk = b.AccSortPk AND  b.AccTyp='B' and a.AccCat=1 and a.ConAcc='Y'  ORDER BY a.AccCode

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccDesc" });
            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfSc.AccTyp", Comparison = QComparison.EQUALS, ColumnValue = "B", ColumnConnector = QLogicOperator.AND });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCat", Comparison = QComparison.EQUALS, ColumnValue = 1, ColumnConnector = QLogicOperator.AND });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.ConAcc", Comparison = QComparison.EQUALS, ColumnValue = "Y" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fGlfAcc", ToTable = "fGlfSc", FromColumn = "AccSortPk", ToColumn = "AccSortPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            Query query = new Query() { TableName = "fGlfAcc ", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get creditor group details
        /// </summary>
        /// <returns></returns>
        public Query LoadCurrencyHelp()
        {
            // Select CurCode,CurDesc From fCurrency ORDER BY CurCode

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "CurPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "CurCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "CurDesc" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "CurCode" });
            Query query = new Query() { TableName = "fCurrency", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray(), WhereClause = whereList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get PettyCash account details
        /// </summary>
        /// <returns></returns>

        private Query LoadPettyCashAccHelp()
        {

            // Select distinct a.AccCode,a.AccDesc From fGlfAcc a, fUsrCosDet b,fGlfSc c Where a.CostPk=b.CostPk and a.AccSortPk=c.AccSortPk ;
            // b.UserId='&cUser' AND c.AccTyp='P' and a.AccCat=1 and a.ConAcc='Y' ORDER BY a.AccCode

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccDesc" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fUsrCosDet.UserId", Comparison = QComparison.EQUALS, ColumnValue = GlobalVariables.LoggedInUser, ColumnConnector = QLogicOperator.AND });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfSc.AccTyp", Comparison = QComparison.EQUALS, ColumnValue = "P", ColumnConnector = QLogicOperator.AND });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCat", Comparison = QComparison.EQUALS, ColumnValue = 1, ColumnConnector = QLogicOperator.AND });
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.ConAcc", Comparison = QComparison.EQUALS, ColumnValue = "Y", ColumnConnector = QLogicOperator.AND });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fGlfAcc", ToTable = "fUsrCosDet", FromColumn = "CostPk", ToColumn = "CostPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });
            joinList.Add(new ColumnWithValue { FromTable = "fGlfAcc", ToTable = "fGlfSc", FromColumn = "AccSortPk", ToColumn = "AccSortPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode", OrderBy = QOrderby.ASCENDING });

            Query query = new Query() { TableName = "fGlfAcc", ColValues = columnList.ToArray(), QType = QueryType.SELECTDISTINCT, JoinStructure = joinList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;

        }


        /// <summary>
        /// Get creditor group details
        /// </summary>
        /// <returns></returns>
        public Query LoadCreditorGroupHelp()
        {
            // Select CrGrCode,CrGrName,AddDate From fCreGrp Order By CrGrCode

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "CreGrpPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "CrGrCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "CrGrName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "AddDate" });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "CrGrCode" });
            Query query = new Query() { TableName = "fCreGrp", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;
        }


        /// <summary>
        /// Get Supplier details
        /// </summary>
        /// <returns></returns>
        private Query LoadSupplierHelp()
        {
            // Select SupCode,SupName,SupAdd1,SupAdd2,SupAdd3 From Fsupplier where status = 'A' ORDER BY SupCode
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "SupplierPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupAdd1" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupAdd2" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupAdd3" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "status", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "SupCode", OrderBy = QOrderby.ASCENDING });
            Query query = new Query() { TableName = "fSupplier", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get Supplier details
        /// </summary>
        private Query LoadActiveSupplierAllDetailsHelp()
        {
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "status", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            Query query = new Query() { TableName = "fSupplier", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
            return query;
        }


        /// <summary>
        /// Get Supplier details
        /// </summary>
        /// <returns></returns>
        private Query LoadAlternativeSupplierHelp()
        {
            // Select SupCode,SupName,SupAdd1,SupAdd2,SupAdd3 From Fsupplier where status = 'A' ORDER BY SupCode
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "SupplierPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupAdd1" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupAdd2" });
            columnList.Add(new ColumnWithValue() { ColumnName = "SupAdd3" });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "SupCode", OrderBy = QOrderby.ASCENDING });
            Query query = new Query() { TableName = "fSupplier", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;
        }


        /// <summary>
        /// Get cost codes for the user.
        /// </summary>
        /// <returns></returns>
        private Query LoadUserCostHelp()
        {
            // Select a.CostCode,a.CostName From fCost a, fUsrCosDet b Where b.UserId='&cUser' AND  a.CostCode=b.CostCode
            // ORDER BY a.CostCode

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fCost.CostPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fCost.CostCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fCost.CostName" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fUsrCosDet.UserId", Comparison = QComparison.EQUALS, ColumnValue = GlobalVariables.LoggedInUser });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fCost", ToTable = "fUsrCosDet", FromColumn = "CostPk", ToColumn = "CostPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fCost.CostCode", OrderBy = QOrderby.ASCENDING });
            Query query = new Query() { TableName = "fCost", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray(), JoinStructure = joinList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get location master details by user
        /// </summary>
        /// <returns></returns>
        private Query LoadLocationHelp()
        {
            // Select a.LocCode,a.LocName From fLocation a, fUsrLocDet b where a.LocCode=b.LocCode AND b.UserId='cUserId'                                                                                                        '"
            // ORDER BY a.LocCode

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fLocation.LocationPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fLocation.LocCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fLocation.LocName" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fUsrLocDet.UserId", Comparison = QComparison.EQUALS, ColumnValue = GlobalVariables.LoggedInUser });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fLocation", ToTable = "fUsrLocDet", FromColumn = "LocationPk", ToColumn = "LocationPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });


            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fLocation.LocCode", OrderBy = QOrderby.ASCENDING });
            Query query = new Query() { TableName = "fLocation", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray(), JoinStructure = joinList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get customer details
        /// </summary>
        /// <returns></returns>
        private Query LoadDebtorHelp()
        {
            // Select DebCode,DebName,debctnam,DebAdd1,DebAdd2,DebAdd3 From fDebtor where status = 'A'
            // ORDER BY DebCode
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "DebPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "DebCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "DebName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "debctnam" });
            columnList.Add(new ColumnWithValue() { ColumnName = "DebAdd1" });
            columnList.Add(new ColumnWithValue() { ColumnName = "DebAdd2" });
            columnList.Add(new ColumnWithValue() { ColumnName = "DebAdd3" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "status", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue { ColumnName = "DebCode" });
            Query query = new Query() { TableName = "fDebtor", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;

        }

        /// <summary>
        /// Get All Active customers details
        /// </summary>
        private Query LoadAllActiveDebtorDetailsHelp()
        {
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "status", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            Query query = new Query() { TableName = "fDebtor", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
            return query;

        }

        /// <summary>
        /// Get Sales Rep's all details
        /// </summary>
        private Query LoadAllSalesRepHelp()
        {
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });

            Query query = new Query() { TableName = "fSalRep", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
            return query;

        }

        /// <summary>
        /// Get reason details...
        /// </summary>
        /// <returns></returns>
        private Query LoadReasonHelp()
        {

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fReason.ReasonPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fReason.ReaCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fReason.ReaName" });


            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "ReaTCode", Comparison = QComparison.EQUALS, ColumnValue = "RT03" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fReason", ToTable = "fReaTyp", FromColumn = "ReaTypePk", ToColumn = "ReaTypePk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue { ColumnName = "ReaCode" });
            Query query = new Query() { TableName = "fReason", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray(), JoinStructure = joinList.ToArray() };
            return query;

        }

        private Query LoadTaxReceivableAccHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccDesc" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fGlfSc.AccTyp", Comparison = QComparison.EQUALS, ColumnValue = "B", ColumnConnector = QLogicOperator.AND });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fGlfAcc", ToTable = "fGlfSc", FromColumn = "AccSortPk", ToColumn = "AccSortPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fGlfAcc.AccCode", OrderBy = QOrderby.ASCENDING });

            Query query = new Query() { TableName = "fGlfAcc", ColValues = columnList.ToArray(), QType = QueryType.SELECTDISTINCT, JoinStructure = joinList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;

        }

        /// <summary>
        /// Get GRN Hed details
        /// </summary>
        /// <returns></returns>
        public Query LoadGRNHedHelp()
        {
            // Thushari:This need to be checked for Distinct.
            // Select DISTINCT a.Refno,a.txndate,a.LocCode,a.supCode,a.CurCode,CostCode From FGrnhed a,
            // fGrnDet b where a.RefNo=b.RefNo and b.BalQty >0.00 Order By Refno Desc

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "FGrnhed.GrnPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "FGrnhed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "FGrnhed.manuref" });
            columnList.Add(new ColumnWithValue() { ColumnName = "FGrnhed.txndate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "FGrnhed.LocationPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fLocation.LocCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "FGrnhed.CurPk" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fGrnDet.BalQty", Comparison = QComparison.GREATERTHAN, ColumnValue = 0.00 });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue { ColumnName = "FGrnhed.manuRef", OrderBy = QOrderby.DESCENDING });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fGrnHed", ToTable = "fGrnDet", FromColumn = "GrnPk", ToColumn = "GrnPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });
            joinList.Add(new ColumnWithValue { FromTable = "fGrnHed", ToTable = "fLocation", FromColumn = "LocationPk", ToColumn = "LocationPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });
            joinList.Add(new ColumnWithValue { FromTable = "fGrnHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });
            joinList.Add(new ColumnWithValue { FromTable = "fGrnHed", ToTable = "fCurrency", FromColumn = "CurPk", ToColumn = "CurPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            Query query = new Query() { TableName = "FGrnhed", QType = QueryType.SELECTDISTINCT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), OrderbyClause = orderList.ToArray(), JoinStructure = joinList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get Cost Center Master details
        /// </summary> 
        public Query LoadCostCentreHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "CostPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "CostCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "CostName" });

            Query query = new Query() { TableName = "fCost", QType = QueryType.SELECT, ColValues = columnList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get all location master details
        /// </summary>
        private Query LoadAllLocationHelp()
        {
            // Select a.LocCode,a.LocName From fLocation a ORDER BY a.LocCode

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "LocationPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "LocCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "LocName" });


            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "LocCode", OrderBy = QOrderby.ASCENDING });

            Query query = new Query() { TableName = "fLocation", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray() };
            return query;
        }

        /// <summary>
        /// Get Bank master details
        /// </summary>
        private Query LoadBankHelp()
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "BankPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "BankCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "BankName" });

            Query query = new Query() { TableName = "fBank", QType = QueryType.SELECT, ColValues = columnList.ToArray() };

            return query;
        }

        /// <summary>
        /// Get Cost Center Master details
        /// </summary>        
        public Query LoadCostCenterDescriptiveHelp()
        {
            string queryString = "SELECT A.CostPk, A.CostCode, A.CostName, A.CostCon " +
                                    ", A.level1, (select  CostCode from dbo.fCost where CostPk = A.level1) as CodeLevel1 " +
                                    ", A.level2, (select  CostCode from dbo.fCost where CostPk = A.level2) as CodeLevel2 " +
                                    ", A.level3, (select  CostCode from dbo.fCost where CostPk = A.level3) as CodeLevel3 " +
                                    ", A.level4, (select  CostCode from dbo.fCost where CostPk = A.level4) as CodeLevel4 " +
                                    ", A.level5, (select  CostCode from dbo.fCost where CostPk = A.level5) as CodeLevel5 " +
                                    ", A.level6, (select  CostCode from dbo.fCost where CostPk = A.level6) as CodeLevel6 " +
                                    ", A.level7, (select  CostCode from dbo.fCost where CostPk = A.level7) as CodeLevel7 " +
                                    ", A.level8, (select  CostCode from dbo.fCost where CostPk = A.level8) as CodeLevel8 " +
                                    " FROM dbo.fCost as A";
            Query query = new Query() { QueryString = queryString, QType = QueryType.SELECTOTHER };
            return query;
        }

        private Query LoadEditPurchaseOrderHelp()
        {
            //SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef FROM Fpohed a,fSupplier s where (a.Appstat=0) and a.Supcode=s.Supcode Order by a.TxnDate Desc,a.RefNo Desc
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fPoHed.Appstat", Comparison = QComparison.EQUALS, ColumnValue = 0 });


            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.PoPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.ManuRef" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fPoHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fPoHed.TxnDate", OrderBy = QOrderby.DESCENDING });
            orderList.Add(new ColumnWithValue() { ColumnName = "fPoHed.RefNo", OrderBy = QOrderby.DESCENDING });

            Query query = new Query() { TableName = "fPoHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        private Query LoadAllocatePurchaseOrderHelp()
        {
            //SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef FROM Fpohed a,fSupplier s where a.Appstat=1 and a.Supcode=s.Supcode Order by a.TxnDate Desc,a.RefNo Desc
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fPoHed.Appstat", Comparison = QComparison.EQUALS, ColumnValue = 1 });


            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.PoPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.ManuRef" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fPoHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fPoHed.TxnDate", OrderBy = QOrderby.DESCENDING });
            orderList.Add(new ColumnWithValue() { ColumnName = "fPoHed.RefNo", OrderBy = QOrderby.DESCENDING });

            Query query = new Query() { TableName = "fPoHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        private Query LoadApprovedPurchaseOrderHelp()
        {
            //SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef FROM Fpohed a,fSupplier s where a.Appstat=2 and a.Supcode=s.Supcode Order by a.TxnDate Desc,a.RefNo Desc
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fPoHed.Appstat", Comparison = QComparison.EQUALS, ColumnValue = 2 });


            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.PoPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.ManuRef" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fPoHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fPoHed.TxnDate", OrderBy = QOrderby.DESCENDING });
            orderList.Add(new ColumnWithValue() { ColumnName = "fPoHed.RefNo", OrderBy = QOrderby.DESCENDING });

            Query query = new Query() { TableName = "fPoHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        private Query LoadUnApprovedPurchaseOrderHelp()
        {
            //SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef FROM Fpohed a,fSupplier s where (a.Appstat=0 or a.Appstat=1) and a.Supcode=s.Supcode Order by a.TxnDate Desc
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fPoHed.Appstat", Comparison = QComparison.EQUALS, ColumnValue = 0, ColumnConnector = QLogicOperator.OR });
            whereList.Add(new ColumnWithValue() { ColumnName = "fPoHed.Appstat", Comparison = QComparison.EQUALS, ColumnValue = 1, ColumnConnector = QLogicOperator.OR });


            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.PoPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.ManuRef" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fPoHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fPoHed.TxnDate", OrderBy = QOrderby.DESCENDING });
            orderList.Add(new ColumnWithValue() { ColumnName = "fPoHed.RefNo", OrderBy = QOrderby.DESCENDING });

            Query query = new Query() { TableName = "fPoHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        private Query LoadAllPurchaseOrderHelp(HelpNames helpName)
        {
            //SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef FROM Fpohed a,fSupplier s where a.Supcode=s.Supcode Order by a.TxnDate Desc,a.RefNo Desc

            string tableName = "fPoHed";

            switch (helpName)
            {
                case HelpNames.AllPurchaseOrderHelp:
                    tableName = "fPoHed"; 
                    break;
                case HelpNames.AllTempPurchaseOrderHelp:
                    tableName = "fPoTHed";
                    break;
            }
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".PoPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".ManuRef" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = tableName, ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = tableName + ".TxnDate", OrderBy = QOrderby.DESCENDING });
            orderList.Add(new ColumnWithValue() { ColumnName = tableName + ".RefNo", OrderBy = QOrderby.DESCENDING });

            Query query = new Query() { TableName = tableName, QType = QueryType.SELECT, ColValues = columnList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        //private Query LoadAllTempPurchaseOrderHelp()
        //{
        //    //SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef FROM FpoThed a,fSupplier s where a.Supcode=s.Supcode Order by a.TxnDate Desc,a.RefNo Desc

        //    List<ColumnWithValue> columnList = new List<ColumnWithValue>();
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fPoTHed.PoPk" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fPoTHed.RefNo" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fPoTHed.TxnDate" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fPoTHed.ManuRef" });

        //    List<ColumnWithValue> joinList = new List<ColumnWithValue>();
        //    joinList.Add(new ColumnWithValue { FromTable = "fPoTHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

        //    List<ColumnWithValue> orderList = new List<ColumnWithValue>();
        //    orderList.Add(new ColumnWithValue() { ColumnName = "fPoTHed.TxnDate", OrderBy = QOrderby.DESCENDING });
        //    orderList.Add(new ColumnWithValue() { ColumnName = "fPoTHed.RefNo", OrderBy = QOrderby.DESCENDING });

        //    Query query = new Query() { TableName = "fPoTHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

        //    return query;
        //}

        private Query LoadAllGoodReceiveNoteHelp()
        {
            //SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef,a.RefNo2 TempRefNo FROM Fgrnhed a,fSupplier s where a.Supcode=s.Supcode Order by a.TxnDate Desc,a.RefNo Desc

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fGrnHed.GrnPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGrnHed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGrnHed.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGrnHed.ManuRef" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGrnHed.RefNo2" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fGrnHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fGrnHed.TxnDate", OrderBy = QOrderby.DESCENDING });
            orderList.Add(new ColumnWithValue() { ColumnName = "fGrnHed.RefNo", OrderBy = QOrderby.DESCENDING });

            Query query = new Query() { TableName = "fGrnHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }


        //private Query LoadTemporaryGoodReceiveNoteHelp()
        //{
        //    //SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef FROM Fgrnthed a,fSupplier s where a.Supcode=s.Supcode Order by a.TxnDate Desc,a.RefNo Desc

        //    List<ColumnWithValue> columnList = new List<ColumnWithValue>();
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fGrnTHed.GrnPk" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fGrnTHed.RefNo" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fGrnTHed.TxnDate" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });
        //    columnList.Add(new ColumnWithValue() { ColumnName = "fGrnTHed.ManuRef" });

        //    List<ColumnWithValue> joinList = new List<ColumnWithValue>();
        //    joinList.Add(new ColumnWithValue { FromTable = "fGrnTHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

        //    List<ColumnWithValue> orderList = new List<ColumnWithValue>();
        //    orderList.Add(new ColumnWithValue() { ColumnName = "fGrnTHed.TxnDate", OrderBy = QOrderby.DESCENDING });
        //    orderList.Add(new ColumnWithValue() { ColumnName = "fGrnTHed.RefNo", OrderBy = QOrderby.DESCENDING });

        //    Query query = new Query() { TableName = "fGrnTHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

        //    return query;
        //}

        private Query LoadAllGoodReceiveReturnNoteHelp()
        {
            // SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef FROM FGrnrhed a,fSupplier s
            // where a.Supcode=s.Supcode Order by a.TxnDate Desc,a.RefNo Desc


            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fGrnrHed.GrnrPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGrnrHed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGrnrHed.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fGrnrHed.ManuRef" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fGrnrHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fGrnrHed.TxnDate", OrderBy = QOrderby.DESCENDING });
            orderList.Add(new ColumnWithValue() { ColumnName = "fGrnrHed.RefNo", OrderBy = QOrderby.DESCENDING });

            Query query = new Query() { TableName = "fGrnrHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        private Query LoadGRNRStkHelp()
        {
            // SELECT LocCode, ItemCode, BalQty,TxnDate, CostPrice, RefNo, StkRecNo From fStkIn Order By RefNo Desc
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fStkIn.LocationPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fStkIn.ItemPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fStkIn.BalQty" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fStkIn.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fStkIn.CostPrice" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fStkIn.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fStkIn.StkRecNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fItems.ItemCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fItems.ItemName" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fStkIn", ToTable = "fItems", FromColumn = "ItemPk", ToColumn = "ItemPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });


            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "RefNo", OrderBy = QOrderby.DESCENDING });

            Query query = new Query() { TableName = "fStkIn", QType = QueryType.SELECT, ColValues = columnList.ToArray(), OrderbyClause = orderList.ToArray(), JoinStructure = joinList.ToArray() };
            return query;
        }

        private Query LoadAllQuotationHelp()
        {
            //SELECT a.RefNo,a.txndate,d.DebCode,d.Debname,a.ManuRef FROM fQuohed a,fdebtor d where a.TxnType='20' and a.debcode=d.debcode Order by a.TxnDate

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fQuoHed.QuoPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fQuoHed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fQuoHed.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fDebtor.DebCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fDebtor.DebName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fQuoHed.ManuRef" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "TxnType", Comparison = QComparison.EQUALS, ColumnValue = 20 });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fQuoHed", ToTable = "fDebtor", FromColumn = "DebPk", ToColumn = "DebPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fQuoHed.TxnDate", OrderBy = QOrderby.DESCENDING });


            Query query = new Query() { TableName = "fQuoHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }


        private Query LoadPoHedHelp()
        {
            //sqlQuery = "Select distinct a.RefNo,a.TxnDate as Date , s.SupName as SupplierName, a.ManuRef as ManualNo,a.SupCode From fPoHed a ,fPoDet b,fSupplier s " 
            // Where a.Refno=b.RefNo and a.SupCode=s.SupCode AND balQty >0.00 AND AppStat=2 Order By RefNo Desc  ";

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.PoPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fPoHed.ManuRef" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName" });


            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "fPoHed.AppStat", Comparison = QComparison.EQUALS, ColumnValue = 2 });
            whereList.Add(new ColumnWithValue() { ColumnName = "fPoHed.BalQty", Comparison = QComparison.GREATERTHAN, ColumnValue = 0.00 });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fPoHed", ToTable = "fPoDet", FromColumn = "PoPk", ToColumn = "PoPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });
            joinList.Add(new ColumnWithValue { FromTable = "fPoHed", ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fPoHed.PoPk", OrderBy = QOrderby.ASCENDING });

            Query query = new Query() { TableName = "fPoHed", QType = QueryType.SELECTDISTINCT, ColValues = columnList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }


        private Query LoadAllGoodReceiveNoteHelp(HelpNames helpName)
        {
            //SELECT a.RefNo,a.txndate,a.SupCode,s.SupName,a.ManuRef,a.RefNo2 TempRefNo FROM Fgrnhed a,fSupplier s where a.Supcode=s.Supcode Order by a.TxnDate Desc,a.RefNo Desc

            string tableName = "fGrnHed";

            switch (helpName)
            {
                case HelpNames.AllGoodReceiveNoteHelp:
                    tableName = "fGrnHed";
                    break;
                case HelpNames.TemporaryGoodReceiveNoteHelp:
                    tableName = "fGrnTHed";
                    break;
            }
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".GrnPk"});
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".RefNo"});
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".TxnDate"});
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupCode"});
            columnList.Add(new ColumnWithValue() { ColumnName = "fSupplier.SupName"});
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".ManuRef"});
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".RefNo2" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = tableName, ToTable = "fSupplier", FromColumn = "SupplierPk", ToColumn = "SupplierPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = tableName + ".TxnDate", OrderBy = QOrderby.DESCENDING });
            orderList.Add(new ColumnWithValue() { ColumnName = tableName + ".RefNo", OrderBy = QOrderby.DESCENDING });

            Query query = new Query() { TableName = tableName, QType = QueryType.SELECT, ColValues = columnList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }

        private Query LoadAllSalesOrderHelp()
        {
            //SELECT a.RefNo,a.txndate,d.DebCode,d.Debname,a.ManuRef FROM fOrdhed a,fdebtor d where a.TxnType='21' and a.debcode=d.debcode Order by a.TxnDate Desc,a.RefNo desc

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "fOrdhed.OrderPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fOrdhed.RefNo" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fOrdhed.TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fDebtor.DebCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fDebtor.DebName" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fOrdhed.ManuRef" });

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "TxnType", Comparison = QComparison.EQUALS, ColumnValue = 21 });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fOrdhed", ToTable = "fDebtor", FromColumn = "DebPk", ToColumn = "DebPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            List<ColumnWithValue> orderList = new List<ColumnWithValue>();
            orderList.Add(new ColumnWithValue() { ColumnName = "fOrdhed.TxnDate", OrderBy = QOrderby.DESCENDING });
            orderList.Add(new ColumnWithValue() { ColumnName = "fOrdhed.RefNo", OrderBy = QOrderby.DESCENDING });


            Query query = new Query() { TableName = "fOrdhed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), JoinStructure = joinList.ToArray(), OrderbyClause = orderList.ToArray() };

            return query;
        }


        private Query LoadPurchaseOrderDetails(HelpNames helpName, object primaryKey)
        {
            //select a.* from fPodet a, fItems b where a.ItemPk = b.ItemPk and a.PoPk=
            string tableName = "fPoDet";

            switch (helpName)
            {
                case HelpNames.PoHedHelp:
                    tableName = "fPoDet";
                    break;
                case HelpNames.PoDetHelpTemp:
                    tableName = "fPoTDet";
                    break;
            }
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = tableName + ".*" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fItems.ItemCode" });
            columnList.Add(new ColumnWithValue() { ColumnName = "fitems.ItemName" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = tableName, ToTable = "fitems", FromColumn = "ItemPk", ToColumn = "ItemPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });
            
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = tableName + ".PoPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKey });

            Query query = new Query() { TableName = tableName, QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };

            return query;
        }
    }
}
